from peewee import *
db = MySQLDatabase('nemtocr', host='localhost', port=3800, user='root', password='!@mtg23YWZ^&')


class UniversityMajorPlan(Model):
    id = PrimaryKeyField()
    is_add = IntegerField()
    page = IntegerField()
    year = IntegerField()
    batch = IntegerField()
    ucode = CharField(max_length=32)
    uname = CharField(max_length=64)
    major_code = CharField(max_length=16)
    major = CharField(max_length=128)
    remarks = TextField()
    tel = CharField(max_length=128)
    major_type = CharField(max_length=16)
    type = IntegerField()
    cut_remarks = TextField()
    plan_years = IntegerField()
    plan_expenses = IntegerField()
    plan_number = IntegerField()
    province = CharField(max_length=16)
    city = CharField(max_length=16)
    province_code = IntegerField()
    city_code = IntegerField()
    is_deleted = IntegerField()

    class Meta:
        database = db  # This model uses the "spider" database.
        table_name = 'university_major_plan'  # 招生计划表


    # 插入单条数据(返回新增id)
    def toInsert(self, row):
        try:
            return UniversityMajorPlan.insert(row).execute()
        except Exception as e:
            return ''


    # 批量插入数据
    def toInsertBatch(self, rows):
        with db.atomic():
            UniversityMajorPlan.insert_many(rows).execute()


    # 根据id，获取单条记录
    def getOne(self, id):
        try:
            return UniversityMajorPlan.select().where(UniversityMajorPlan.id == id).dicts().get()
        except Exception as e:
            return ''


    # 根据年份查询列表
    def getList(self, year):
        try:
            return UniversityMajorPlan.select(
                UniversityMajorPlan.year, UniversityMajorPlan.batch, UniversityMajorPlan.ucode,
                UniversityMajorPlan.uname, UniversityMajorPlan.major, UniversityMajorPlan.type,
                UniversityMajorPlan.major_code, UniversityMajorPlan.plan_number, UniversityMajorPlan.province_code,
                UniversityMajorPlan.city_code, UniversityMajorPlan.province, UniversityMajorPlan.city,
                UniversityMajorPlan.cut_remarks).where(UniversityMajorPlan.year == year).dicts().execute()
        except Exception as e:
            return ''


    # 根据年份查询列表
    def getListAll(self, year):
        try:
            return UniversityMajorPlan.select().where(UniversityMajorPlan.year == year).order_by(UniversityMajorPlan.id.asc()).dicts().execute()
        except Exception as e:
            print(e)
            return ''


    # 根据年份查询列表
    def getListAll2(self, year):
        try:
            return UniversityMajorPlan.select(UniversityMajorPlan.id, UniversityMajorPlan.remarks,
                                              UniversityMajorPlan.batch, UniversityMajorPlan.ucode,
                                              UniversityMajorPlan.type, UniversityMajorPlan.major_code,
                                              ).where(
                UniversityMajorPlan.year == year).order_by(UniversityMajorPlan.id.asc()).dicts().execute()
        except Exception as e:
            print(e)
            return ''

    # 根据年份查询列表
    def getListAll3(self, year, is_add):
        try:
            return UniversityMajorPlan.select(UniversityMajorPlan.id,
                                              UniversityMajorPlan.batch, UniversityMajorPlan.ucode,
                                              UniversityMajorPlan.uname, UniversityMajorPlan.major,
                                              UniversityMajorPlan.type, UniversityMajorPlan.major_code,
                                              UniversityMajorPlan.plan_number, UniversityMajorPlan.cut_remarks,
                                              UniversityMajorPlan.province_code, UniversityMajorPlan.city_code,
                                              UniversityMajorPlan.province, UniversityMajorPlan.city,
                                              ).where(
                UniversityMajorPlan.year == year & UniversityMajorPlan.is_add == is_add).order_by(UniversityMajorPlan.id.asc()).dicts().execute()
        except Exception as e:
            print(e)
            return ''


    # 根据年份、批次、文理科类型、院校代码统计记录数
    def getOneByYearBatch(self, year, batch, type, code, major):
        try:
            return UniversityMajorPlan.select().where((UniversityMajorPlan.year == year) &
                                                      (UniversityMajorPlan.batch == batch) &
                                                      (UniversityMajorPlan.type == type) &
                                                      (UniversityMajorPlan.ucode == code) &
                                                      (UniversityMajorPlan.major == major)).dicts().get()
        except Exception as e:
            return ''


    # 根据年份、批次、文理科类型、院校代码统计记录数
    def getCount(self, year, batch, type, code, major):
        try:
            return UniversityMajorPlan.select().where((UniversityMajorPlan.year == year) &
                                                      (UniversityMajorPlan.batch == batch) &
                                                      (UniversityMajorPlan.type == type) &
                                                      (UniversityMajorPlan.ucode == code) &
                                                      (UniversityMajorPlan.major == major)).count()
        except Exception as e:
            return ''


    # 更新单条数据(返回影响记录数)
    def toUpdate(self, row, id):
        try:
            return UniversityMajorPlan.update(
                year=row['year'], batch=row['batch'],
                major_type=row['major_type'], type=row['type'], ucode=row['ucode'],
                uname=row['uname'], major=row['major'], major_code=row['major_code'],
                remarks=row['remarks'], tel=row['tel'],
                cut_remarks=row['cut_remarks'], plan_years=row['plan_years'],
                plan_expenses=row['plan_expenses'], plan_number=row['plan_number'],
                province=row['province'], city=row['city'], province_code=row['province_code'], city_code=row['city_code']
            ).where(UniversityMajorPlan.id == id).execute()
        except Exception as e:
            return ''    # 根据年份、批次、文理科类型、院校代码统计记录数


    # 更新单条数据(返回影响记录数)
    def toUpdatePlanNumber(self, row):
        try:
            return UniversityMajorPlan.update(
                plan_number=row['plan_number']
            ).where(
                (UniversityMajorPlan.id == row['id'])
            ).execute()
        except Exception as e:
            return ''

if __name__ == '__main__':
    universityMajorPlan = UniversityMajorPlan()
    count = universityMajorPlan.getOneByYearBatch(2021, 9, 1, 10001, '理科试验班类(元培)')